package com.tuo51.evaluate.service.impl;


import com.matt.commons.page.PagerQuery;
import com.matt.domain.courseV2.VendorCourse;
import com.matt.domain.courseV2.vo.VendorCourseVO;
import com.matt.repository.base.BaseNativeSqlRepository;
import com.tuo51.evaluate.domain.po.EvaluateStatisticsPO;
import com.tuo51.evaluate.service.EvaluateStatisticsService;
import com.tuo51.foudation.utils.DateUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.PageRequest;
import org.springframework.stereotype.Service;

import javax.transaction.Transactional;
import java.util.*;

/**
 * *******************************
 * Created by Ron.
 * form com.tuo51
 * on 2017/7/26
 * *******************************
 */
@Service
@Transactional
public class EvaluateStatisticsServiceImpl implements EvaluateStatisticsService {

    @Autowired
    private BaseNativeSqlRepository baseNativeSqlRepository;

    @Override
    public Page<EvaluateStatisticsPO> getEvaluateCount(String areaId, PagerQuery pagerQuery ) {
        String sql = String.format("SELECT a.id as classId,a.className,a.totalCount,IFNULL(b.evaluationCount,0)evaluationCount,IFNULL(b.evaluationDay,0) evaluationDay,IFNULL(c.partakeCount,0) partakeCount \n" +
                "  FROM ( SELECT COUNT(c.id) as totalCount,cs.`name` as className ,cs.id FROM children c LEFT JOIN  rel_classes_children cc ON c.id = cc.childrenId LEFT JOIN  classes cs ON cs.id = cc.classesId \n" +
                "WHERE  cs.areaId = '%s' and  cs.id is not null group by cs.id  order by cs.deleted asc, cs.category asc, cs.created desc) a LEFT JOIN  ( " +
                "SELECT COUNT(er.id) as evaluationCount,DATEDIFF(NOW(), max(er.created)) as evaluationDay,c.id  from tb_evaluate_record  er INNER JOIN  rel_classes_children cc ON er.childrenId = cc.childrenId INNER JOIN classes c ON c.id = cc.classesId \n" +
                "where c.areaId = '%s' \n" +
                "group by c.id  ) b ON a.id = b.id  LEFT JOIN \n" +
                "\n" +
                "(SELECT COUNT(er.id) as partakeCount,c.id  from tb_evaluate_record  er INNER JOIN  rel_classes_children cc ON er.childrenId = cc.childrenId INNER JOIN classes c ON c.id = cc.classesId \n" +
                "where c.areaId = '%s' \n" +
                "group by c.id,er.childrenId ) c  ON b.id = c.id  group by a.id  ", areaId, areaId, areaId);
        List<Object[]> objects1 = baseNativeSqlRepository.sqlArrayList(sql);
        sql += String.format(" limit %s, %s ", pagerQuery.getPageNum() * pagerQuery.getPageSize(), pagerQuery.getPageSize());
        List<Object[]> objects = baseNativeSqlRepository.sqlArrayList(sql);

        List<EvaluateStatisticsPO> list = new ArrayList<>();
        for (int j = 0; j < objects.size(); j++) {
            EvaluateStatisticsPO es = new EvaluateStatisticsPO();
            es.setClassId(objects.get(j)[0].toString());
            es.setClassName(objects.get(j)[1].toString());
            es.setTotalCount(Integer.valueOf(objects.get(j)[2].toString()));
            es.setEvaluationCount(Integer.valueOf(objects.get(j)[3].toString()));
            es.setEvaluationDay(Integer.valueOf(objects.get(j)[4].toString()));
            es.setPartakeCount(Integer.valueOf(objects.get(j)[5].toString()));
            list.add(es);
        }
        return  new PageImpl<>(list, PageRequest.of(pagerQuery.getPageNum(), pagerQuery.getPageSize()), objects1.size());
    }
}
